package com.example.springboot.mapper;


import com.example.springboot.entity.Finance;
import com.example.springboot.entity.Inventory;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface FinanceMapper {

    @Select("SELECT * FROM finances")
    List<Finance> getAllFinances();



    @Select("SELECT * FROM finances WHERE id = #{id}")
    List<Finance> getFinanceByid(@Param("id") Long id);

    @Select("select * from finances where name = #{name}")
    List<Finance> getFinanceByname(String name);

    @Select("select * from finances where amount = #{amount}")
    List<Finance> getFinanceByamount(double amount);

    @Select("select * from finances where type = #{type}")
    List<Finance> getFinanceBytype(String type);

    @Select("select * from finances where date = #{date}")
    List<Finance> getFinanceBydate(String date);


    @Insert("INSERT INTO finances(name, amount, date, type, remark) VALUES(#{name}, #{amount}, #{date}, #{type}, #{remark})")
    void addFinance(Finance finance);

    @Update("update finances SET name = #{name}, amount = #{amount}, date = #{date}, type = #{type}, remark = #{remark} WHERE id = #{id}")
    void editFinance(Finance finance);

    @Update("UPDATE finances SET name = #{name}, amount = #{amount}, date = #{date}, type = #{type}, remark = #{remark} WHERE id = #{id}")
    void updateFinance(Finance finance);

    @Delete("DELETE FROM finances WHERE id = #{id}")
    void deleteFinance(int id);

//    @Select("SELECT * FROM finances WHERE SUBSTRING(date, 1, 4) = #{year} AND SUBSTRING(date, 6, 2) = #{month}")
//    List<Finance> getFinanceByYearMonth(int year, int month);
    @Select("SELECT * FROM finances WHERE MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesByMonth(Integer year, Integer month);

    @Select("SELECT * FROM finances WHERE id = #{id} and MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesByidAndMonth(Long id, Integer year, Integer month);

    @Select("SELECT * FROM finances WHERE name = #{name} and MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesBynameAndMonth(String name, Integer year, Integer month);

    @Select("SELECT * FROM finances WHERE amount = #{amount} and MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesByamountAndMonth(String amount, Integer year, Integer month);

    @Select("SELECT * FROM finances WHERE type = #{type} and MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesBytypeAndMonth(String type, Integer year, Integer month);

    @Select("SELECT * FROM finances WHERE date = #{date} and MONTH(date) = #{month} AND YEAR(date) = #{year}")
    List<Finance> getFinancesBydateAndMonth(String date, Integer year, Integer month);


}
